<?php
include 'configs/Config.php';
include 'Order.php';
include 'Room.php';
include 'User.php';

class Model{
	private $DB_NAME;
	private $DB_USER;
	private $DB_PASSWORD;
	private $DB_HOST;

	private $mysqli;

	function __construct() {
		$this->DB_NAME = DB_NAME;
		$this->DB_USER = DB_USER;
		$this->DB_PASSWORD = DB_PASSWORD;
		$this->DB_HOST = DB_HOST;
		$this->mysqli = new mysqli ( $this->DB_HOST, $this->DB_USER, $this->DB_PASSWORD, $this->DB_NAME );
		if (mysqli_connect_errno ()) {
			printf ( "Connect failed: %s\n", mysqli_connect_error () );
			exit ();
		}
		$this->mysqli->set_charset ( "utf8" );
	}

	function __destruct() {
		$this->mysqli->close ();
	}
	public function addUser($newUser) {
		//var_dump($newUser);
		$result = $this->mysqli->prepare ( 'INSERT INTO user ( userAccName,
				userPassword,userKind,userName,userTel,userEmail) VALUES (?,?,?,?,?,?)' );
		if ($result) {
			$b=$newUser->getUserAccName();
			$c=$newUser->getUserPassword();
			$d=$newUser->getUserKind();
			$f=$newUser->getUserName();
			$g=$newUser->getUserTel();
			$h=$newUser->getUserEmail();
			$result->bind_param ('ssssss',$b,$c,$d,$f,$g,$h);
			//var_dump($result);
			$result->execute ();
			$flag = $result->affected_rows;
			if (($flag == 0) || ($flag == - 1)) {
				echo 'Add New User Fail';
				$result->close ();
				return - 1;
			}
			$result->close ();
		}
	}
	public function userPenalty($userId,$date,$unauthorizedTime)
	{
		$result = $this->mysqli->prepare ( 'INSERT INTO penalty ( userId,
				date,unauthorizedTime) VALUES (?,?,?)' );
		if ($result) {
			$result->bind_param ('ssi',$userId,$date,$unauthorizedTime);
			//var_dump($result);
			$result->execute ();
			$flag = $result->affected_rows;
			if (($flag == 0) || ($flag == - 1)) {
				echo 'Penalty fail';
				$result->close ();
				return - 1;
			}
			$result->close ();
		}
	}

	public function addRoom($newRoom) {
		$result = $this->mysqli->prepare ( 'INSERT INTO room (
				roomName,roomCost,roomSeatCount) VALUES (?,?,?)' );
		if ($result) {
			$c=$newRoom->getRoomName();
			$d=$newRoom->getRoomCost();
			$e=$newRoom->getRoomSeatCount();
			$result->bind_param ('sss', $c,$d,$e);
			//var_dump($result);
			$result->execute ();
			$flag = $result->affected_rows;
			if (($flag == 0) || ($flag == - 1)) {
				//echo 'Add New Room Fail';
				$result->close ();
				return - 1;
			}
			$result->close ();
		}
		return 0;
	}

	public function addOrder($newOrder) {
		$result = $this->mysqli->prepare ( 'INSERT INTO `meetingroom`.`order` (`roomId`, `userId`, `orderStartTime`, `orderEndTime`, `orderStatus`)
										 VALUES (?,?,?,?,?)' );
		if ($result) {
			//$a=$newOrder->getOrderId();
			$b=$newOrder->getRoomId();
			$c=$newOrder->getUserId();
			$d=$newOrder->getOrderStartTime();
			$e=$newOrder->getOrderEndTime();
			$f=$newOrder->getOrderStatus();
			//$g=$newOrder->getOrderRealCheckIn();
			//$h=$newOrder->getOrderRealCheckOut();
			$result->bind_param ('iissi', $b,$c,$d,$e,$f);
			//var_dump($result);
			$result->execute ();
			$flag = $result->affected_rows;
			if (($flag == 0) || ($flag == - 1)) {
				echo 'Add New Order Fail';
				$result->close ();
				return - 1;
			}
			$result->close ();
			return 0;
		}
	}

	public function deleteRoom($roomId){
		$result=$this->mysqli->prepare("DELETE FROM room where roomId=?");
		if($result){
			$result->bind_param('i',$roomId);
			$result->execute();
			$flag=$result->affected_rows;
			if(($flag==0)||($flag==-1)){
				echo 'delete room fail';
				$result->close();
				return -1;
			}
			return 0;
		}
	}

	public function searchAllUser(){
		$result = $this->mysqli->prepare ( 'SELECT * FROM user' );
		if($result){
			$result->execute ();
			$result->bind_result ( $userID, $userAccName, $userPassword, $userKind, $userName, $userTel, $userEmail);
			//printf("\n %s \n",$userID);
			while($result->fetch ()){
				$user [] = new user ($userID, $userAccName, $userPassword, $userKind, $userName, $userTel, $userEmail);
			}
			$result->close();
			return $user;
		}
	}
	public function searchAllRoom()
	{
		$result = $this->mysqli->prepare ( 'SELECT * FROM room' );
		if($result){
			$result->execute();
			$result->bind_result($roomId,$roomCategory,$roomName,$roomCost,$roomSeatCount);
			while($result->fetch())
			{
				$room[]=new Room($roomId,$roomCategory,$roomName,$roomCost,$roomSeatCount);
			}
			return $room;
		}
	}
	public function searchUserAccName($userAccName){
		$result=$this->mysqli->prepare('SELECT userId,
				userPassword,userKind,userName,userTel,userEmail FROM `meetingroom`.`user` WHERE userAccName=?');
		if($result){
			$result->bind_param('s',$userAccName);
			$result->execute();
			//echo $result->affected_rows;
			$result->bind_result ($userId, $userPassword, $userKind, $userName, $userTel, $userEmail);
			$result->fetch ();
			$user = new User ( $userId, $userAccName, $userPassword, $userKind, $userName, $userTel, $userEmail );
			$result->close();
			return $user;
		}
	}

	public function searchUserById($userId){
		$result=$this->mysqli->prepare('SELECT userAccName,
				userPassword,userKind,userName,userTel,userEmail FROM user WHERE userId=?');
		if($result){
			$result->bind_param('i',$userId);
			$result->execute();
			$result->bind_result ( $userAccName, $userPassword, $userKind, $userName, $userTel, $userEmail);
			$result->fetch ();
			$user = new User ( $userId, $userAccName, $userPassword, $userKind, $userName, $userTel, $userEmail );
			$result->close();
			return $user;
		}
	}

	public function searchUserByMail($email){
		$result=$this->mysqli->prepare('SELECT userId,userAccName,
				userPassword,userKind,userName,userTel FROM user WHERE userEmail=?');
		if($result){
			$result->bind_param('s',$email);
			$result->execute();
			$result->bind_result ( $userID, $userAccName, $userPassword, $userKind, $userName, $userTel);
			$result->fetch ();
			//var_dump($result);
			$user = new User ( $userID, $userAccName, $userPassword, $userKind,  $userName, $userTel, $email );
			//var_dump($user);
			$result->close();
			return $user;
		}
	}
	public function searchUserByName($name){
		$result=$this->mysqli->prepare('SELECT userId,userAccName,
				userPassword,userKind,userName,userTel,userEmail FROM user WHERE userName LIKE ?');
		if ($result) {
			$result->bind_param ( 's', $name );
			$name="%$name%";
			//var_dump($result);
			$result->execute ();
			//$count = $result->num_rows;
			$result->bind_result ( $userID, $userAccName, $userPassword, $userKind, $userCompanyName, $userName, $userTel, $userEmail);
			//printf("\n %s \n",$userID);
			while($result->fetch ()){
				$user[] = new User ($userID, $userAccName, $userPassword, $userKind, $userCompanyName, $userName, $userTel, $userEmail);
			}
			$result->close();
			return $user;
		}
	}

	public function searchRoomByName($name){
		$result=$this->mysqli->prepare('SELECT roomId,roomCategory,
				roomCost,roomSeatCount FROM room WHERE roomName=?');
		if($result){
			$result->bind_param('s',$name);
			$result->execute();
			$result->bind_result ( $roomId,$roomCategory,$roomCost,$roomSeatCount);
			$result->fetch ();
			$room = new Room ( $roomId,$roomCategory,$name,$roomCost,$roomSeatCount );
			$result->close;
			return $room;
		}
	}

	public function searchAvailableOrderByRoomId($roomId){
		$result=$this->mysqli->prepare('SELECT orderId,userId,orderStartTime,orderEndTime,orderStatus,orderRealCheckIn,orderRealCheckOut
				FROM `meetingroom`.`order` WHERE roomId=?');
		if($result){
			$result->bind_param('i', $roomId);
			$result->execute ();
			$result->bind_result ( $orderId, $userId, $orderStartTime, $orderEndTime, $orderStatus, $orderRealCheckIn, $orderRealCheckOut);
			//printf("\n %s \n",$userID);
			while($result->fetch ()){
				$order [] = new Order ($orderId, $userId, $roomId, $orderStartTime, $orderEndTime, $orderStatus, $orderRealCheckIn, $orderRealCheckOut);
			}
			$result->close();
			return $order;
		}
	}

	public function searchAvailable($date, $roomId){
		$startTime=$date." 00:00:00";
		$endTime=$date." 23:59:59";
		$result = $this->mysqli->prepare ( 'SELECT orderId,userId,orderStartTime,orderEndTime,orderStatus,orderRealCheckIn,orderRealCheckOut FROM `meetingroom`.`order` WHERE roomId= ? AND orderStartTime <= ? AND orderStartTime >= ?
				order by orderStartTime DESC' );
		if($result){
			$result->bind_param('iss', $roomId,$endTime,$startTime);
			$result->execute ();
			$result->bind_result ( $orderId, $userId, $orderStartTime, $orderEndTime, $orderStatus, $orderRealCheckIn, $orderRealCheckOut);
			//printf("\n %s \n",$userID);
			while($result->fetch ()){
				$order [] = new Order ($orderId, $roomId, $userId, $orderStartTime, $orderEndTime, $orderStatus, $orderRealCheckIn, $orderRealCheckOut);
			}
			$result->close();
			return $order;
		}
	}

	public function searchOrderByUserId($month, $userId){
		$startTime=$month."-00 00:00:00";
		$endTime=$month."-31 23:59:59";
		$result = $this->mysqli->prepare ( 'SELECT orderId,roomId,orderStartTime,orderEndTime,orderStatus,orderRealCheckIn,orderRealCheckOut FROM `meetingroom`.`order` WHERE userId= ? AND orderStartTime <= ? AND orderStartTime >= ?
				order by orderStartTime DESC' );
		if($result){
			$result->bind_param('iss', $userId,$endTime,$startTime);
			$result->execute ();
			$result->bind_result ( $orderId, $roomId, $orderStartTime, $orderEndTime, $orderStatus, $orderRealCheckIn, $orderRealCheckOut);
			//printf("\n %s \n",$userID);
			while($result->fetch ()){
				$order [] = new Order ($orderId, $roomId, $userId, $orderStartTime, $orderEndTime, $orderStatus, $orderRealCheckIn, $orderRealCheckOut);
			}
			$result->close();
			return $order;
		}
	}
	public function showAllUserId()
	{
		$result=$this->mysqli->prepare('Select userId FROM `meetingroom`.`user`');
		if($result)
		{
			$result->execute();
			$result->bind_result($userId);
			while($result->fetch())
			$userIdArr[]=$userId;
			$result->close;
			return $userIdArr;
		}

	}
	public function showUserPenalty($userId,$month)
	{
		$startTime=$month."-00";
		$endTime=$month."-31";
		$result=$this->mysqli->prepare('Select unauthorizedTime FROM `meetingroom`.`penalty` WHERE userId=? AND date <= ? AND date >= ?
				order by date DESC ');
		if($result)
		{
			$result->bind_param('iss',$userId,$endTime,$startTime);
			$result->execute();
			$result->bind_result($unauthorizedTime);
			while($result->fetch())
			$unauthorizedTimeArr[]=$unauthorizedTime;
			$result->close;
			return $unauthorizedTimeArr;
		}

	}
	public function getUserById($userId){
		$result = $this->mysqli->prepare ( 'SELECT userId,userAccName,
				userPassword,userKind,userName,userTel,userEmail FROM user where userId =?' );
		if ($result) {
			$result->bind_param ( 'i', $userId );
			$result->execute ();
			$result->bind_result ( $userId, $userAccName, $userPassword, $userKind, $userCompanyName, $userName, $userTel, $userEmail);
			$result->fetch ();
			$user = new User ( $userId, $userAccName, $userPassword, $userKind, $userCompanyName, $userName, $userTel, $userEmail );
			$result->close();
			return $user;
		}
	}
	public function getRoomById($roomId){
		$result = $this->mysqli->prepare ( 'SELECT roomId,roomCategory,
				roomName,roomCost,roomSeatCount FROM room where roomId =?' );
		if ($result) {
			$result->bind_param ( 'i', $roomId );
			$result->execute ();
			$result->bind_result ( $roomId, $roomCategory, $roomName, $roomCost, $roomSeatCount);
			$result->fetch ();
			$room = new Room( $roomId, $roomCategory, $roomName, $roomCost, $roomSeatCount);
			$result->close();
			return $room;
		}
	}

	public function getOrderById($orderId){
		$result = $this->mysqli->prepare ( 'SELECT orderId,roomId, userId,orderStartTime,
				orderEndTime,orderStatus,orderRealCheckIn, orderRealCheckOut FROM `meetingroom`.`order` WHERE orderId =?' );
		if ($result) {
			$result->bind_param ( 'i', $orderId );
			$result->execute ();
			$result->bind_result ( $orderId,$roomId, $userId,$orderStartTime,$orderEndTime,$orderStatus,$orderRealCheckIn, $orderRealCheckOut);
			$result->fetch ();
			$order = new Order ($orderId,$roomId, $userId,$orderStartTime,$orderEndTime,$orderStatus,$orderRealCheckIn, $orderRealCheckOut);
			$result->close();
			return $order;
		}
	}




	public function updateUser($user){
		$result = $this->mysqli->prepare ( 'UPDATE user SET userAccName=?,
				userPassword=?,userKind=?,userName=?,userTel=?,userEmail=? WHERE userId=?' );
		//$userID, $userLogin, $userName, $userSeniorID, $userEmail, $userSchoolYear, $userBirthday, $userSex
		if ($result) {
			$a=$user->getUserAccName();
			$b=$user->getUserPassword();
			$c=$user->getUserKind();
			$e=$user->getUserName();
			$f=$user->getUserTel();
			$g=$user->getUserEmail();
			$h=$user->getUserId();
			$result->bind_param ( 'ssssssi',$a,$b,$c,$e,$f,$g,$h);
			$result->execute ();
			$flag = $result->affected_rows;
			if (($flag == 0) || ($flag == - 1)) {
				//echo 'update user account  fail';
				$result->close ();
				return - 1;
			}
			$result->close ();
		}
		return 0;
	}
	public function updateFee($orderFee,$cancelFee)
	{
		$result = $this->mysqli->prepare ( 'UPDATE fee SET orderFee=?,cancelFee=?' );
		//$userID, $userLogin, $userName, $userSeniorID, $userEmail, $userSchoolYear, $userBirthday, $userSex
		if ($result) {
			$result->bind_param ( 'ii',$orderFee,$cancelFee);
			$result->execute ();
			$flag = $result->affected_rows;
			if (($flag == 0) || ($flag == - 1)) {
				//echo 'update user account  fail';
				$result->close ();
				return - 1;
			}
			$result->close ();
		}
		return 0;
	}
	public function getOrderFee()
	{
		$result=$this->mysqli->prepare('Select orderFee FROM `meetingroom`.`fee`');
		if($result)
		{
			$result->execute();
			$result->bind_result($orderFee);
			$result->fetch();
			return $orderFee;
		}	
	}
	public function getCancelFee()
	{
		$result=$this->mysqli->prepare('Select cancelFee FROM `meetingroom`.`fee`');
		if($result)
		{
			$result->execute();
			$result->bind_result($cancelFee);
			$result->fetch();
			return $cancelFee;
		}	
	}
	public function updateRoomInfo($room){
		$result = $this->mysqli->prepare ( 'UPDATE room SET roomCategory=?,
				roomName=?,roomCost=?,roomSeatCount=? WHERE roomId=?' );
		if($result){
			$a=$room->getRoomCategory();
			$b=$room->getRoomName();
			$c=$room->getRoomCost();
			$d=$room->getRoomSeatCount();
			$e=$room->getRoomId();
			$result->bind_param('sssss', $a,$b,$c,$d,$e);
			$result->execute();
			$flad=$result->affected_rows;
			if(($flad==0)||($flad==-1)){
			//echo 'update room info fail';
				$result->close();
				return -1;
			}
			$result->close();
		}
		return 0;
	}

	public function updateOrder($order){
		$result = $this->mysqli->prepare ( 'UPDATE `meetingroom`.`order` SET roomId=?,userId=?,
				orderStartTime=?,orderEndTime=?,orderStatus=?,orderRealCheckIn=?,orderRealCheckOut=? WHERE orderId=?' );
		if($result){
			$a=$order->getUserId();
			$b=$order->getRoomId();
			$c=$order->getOrderStartTime();
			$d=$order->getOrderEndTime();
			$e=$order->getOrderStatus();
			$f=$order->getOrderRealCheckIn();
			$g=$order->getOrderRealCheckOut();
			$h=$order->getOrderId();
			$result->bind_param('ssssssss', $a,$b,$c,$d,$e,$f,$g,$h);
			$result->execute();
			$flad=$result->affected_rows;
			if(($flad==0)||($flad==-1)){
				echo 'update order fail';
				$result->close();
				return -1;
			}
			$result->close();
		}
		return 0;
	}

}
